// ================================================================================
// 		File: PartyGrassrootsGroupReport.cs
// 		Desc: 
//  
// 		Called by:   
//               
// 		Auth: chenwenzhi（）
// 		Date: 2016-06-11 23:56
// ================================================================================
// 		Change History
// ================================================================================
// 		Date:		Author:				Description:
// 		--------	--------			-------------------
//    
// ================================================================================
// 
// ================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using ZHDJ.Core;

namespace ZHDJ.Data.Models.Grassroots
{
    /// <summary>
    /// 月报
    /// </summary>   
    public class PartyGrassrootsGroupReport : EntityBase
    {
        #region 属性

        /// <summary>
        /// 所属分组
        /// </summary>
        public string ItemID { get; set; }

        /// <summary>
        /// 负责人
        /// </summary>
        public string Chief { get; set; }

        /// <summary>
        /// 月报时间
        /// </summary>
        public DateTime? ReportTime { get; set; }

        /// <summary>
        /// 接待群众－人次
        /// </summary>
        public int jiedaiRenci { get; set; }

        /// <summary>
        /// 接待群众－基数
        /// </summary>
        public int jiedaiJishu { get; set; }

        /// <summary>
        /// 接待群众－接待率
        /// </summary>
        public double jiedaiLu { get; set; }

        /// <summary>
        /// 入户群众－户次
        /// </summary>
        public int ruhuHuci { get; set; }

        /// <summary>
        /// 入户群众－基数
        /// </summary>
        public int ruhuJishu { get; set; }

        /// <summary>
        /// 入户群众－入户率
        /// </summary>
        public double ruhuLu { get; set; }

        /// <summary>
        /// 发放民情联系卡张数
        /// </summary>
        public int nianxika { get; set; }

        /// <summary>
        /// 收集意见建议条数
        /// </summary>
        public int jianyishu { get; set; }

        /// <summary>
        /// 解决问题件数
        /// </summary>
        public int jiejueWentishu { get; set; }

        /// <summary>
        /// 备注
        /// </summary>
        public string Remark { get; set; }

        /// <summary>
        /// 创建用户
        /// </summary>
        public string CreateBy { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 更新时间
        /// </summary>
        public DateTime? ModifyTime { get; set; }

        /// <summary>
        /// 修改用户
        /// </summary>
        public string ModifyBy { get; set; }

        #endregion

        #region 查询管理

        public static DataSet GetGridData(int pageIndex, int pageSize, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "Begin",Value = (pageIndex)*pageSize},
                new MySqlParameter(){DbType = DbType.Int32,ParameterName = "PageSize",Value = pageSize}
            };


            StringBuilder sb = new StringBuilder();
            sb.Append(@"select SQL_CALC_FOUND_ROWS  data.ID,data.ItemID,data.Chief,data.ReportTime,data.jiedaiRenci,data.jiedaiJishu,data.jiedaiLu,data.ruhuHuci,data.ruhuJishu,data.ruhuLu,data.nianxika,data.jianyishu,data.jiejueWentishu,data.CreateBy,data.CreateTime,data.ModifyTime,data.ModifyBy
                        from tblPartyGrassrootsGroupReport data
                        where 1=1 ");
            //if (!string.IsNullOrEmpty(searchKey)) 
            //{
            //    sb.Append(" and a.Name like ?key ");
            //    parameters.Add(
            //        new MySqlParameter(){DbType = DbType.String,ParameterName = "key",Value = string.Format("%{0}%", searchKey)});
            //}

            sb.Append(" order by data.CreateTime desc LIMIT ?Begin ,?PageSize;SELECT FOUND_ROWS();");
            return MySqlHelper.ExecuteDataSet(sb.ToString(), parameters.ToArray());
        }

        public static DataSet GetAllGroups(string groupId)
        {

            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@" select ID as GroupID,GroupName ,
                    (select count(i.ID) from tblPartyGrassrootsGroupItem i 
where i.State=4 and  i.GroupID=data.ID)
                    ZhudianCount  from tblPartyGrassrootsGroup data  where data.State=4 ");

            if (!string.IsNullOrEmpty(groupId))
            {


                sbSql.AppendFormat(@" and  data.ID in ({0}) ", groupId);
            }

            //sbSql.Append(@" order by OrderNo ");

            return MySqlHelper.ExecuteDataSet(sbSql.ToString());

        }


        /// <summary>
        /// 获取各组织的情况统计数据
        /// </summary>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public static DataSet GetStatisticsReportData(string groupID, string statrTime, string endTime)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append(@"
                                select 
                                sum(jiedaiRenci) as jiedaiRenci,
                                sum(ruhuHuci) as ruhuHuci,
                                
                                
                                sum(jianyishu) as jianyishu,
                                sum(jiejueWentishu) as jiejueWentishu,
                                sum(nianxika) as nianxika
                               
                                from tblPartyGrassrootsGroupReport where 1=1 ");
            if (!string.IsNullOrEmpty(groupID))
            {
                sbSql.Append(
                    " and  ItemID in (select ID from tblPartyGrassrootsGroupItem where GroupID=?groupID and State=4) ");
                parameters.Add(new MySqlParameter() {DbType = DbType.String, ParameterName = "groupID", Value = groupID});
            }

            if (!string.IsNullOrEmpty(statrTime))
            {
                sbSql.Append(" and  ReportTime between ?startTime and ?endTime");

                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.DateTime,
                    ParameterName = "startTime",
                    Value = statrTime
                });
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.DateTime,
                    ParameterName = "endTime",
                    Value = endTime
                });
            }
            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());

        }
        /// <summary>
        /// 获取天河区情况统计数据
        /// </summary>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public static DataSet GetAllStatisticsReportData(string statrTime, string endTime)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            StringBuilder sbSql = new StringBuilder();
                sbSql.Append(@"
                                select    
                                sum(jiedaiRenci) as jiedaiRenci,
sum(ruhuHuci) as ruhuHuci,
                                
                                sum(jianyishu) as jianyishu,
                                sum(jiejueWentishu) as jiejueWentishu,
                                sum(nianxika) as nianxika
                               
                                from tblPartyGrassrootsGroupReport where ItemID in (select ID from tblPartyGrassrootsGroupItem 
                                where GroupID in (select ID from tblPartyGrassrootsGroup where State=4 ) and State=4) ");

            if (!string.IsNullOrEmpty(statrTime))
            {
                sbSql.Append(" and  ReportTime between ?startTime and ?endTime");

                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.DateTime,
                    ParameterName = "startTime",
                    Value = statrTime
                });
                parameters.Add(new MySqlParameter()
                {
                    DbType = DbType.DateTime,
                    ParameterName = "endTime",
                    Value = endTime
                });
            }

            return MySqlHelper.ExecuteDataSet(sbSql.ToString(), parameters.ToArray());
        

        }
        #endregion
    }
}
